pacman::p_load(tidyverse, data.table, broom)
rm(list=ls())
################################################################################



################################### County-level

for (n in c(1,2,3,4)){

  if(n==1){df = setDT(read.csv(gzfile("../../data/agribusiness/clean/soybean_argentina.csv.gz","rt"), header=T))}
  if(n==2){df = setDT(read.csv(gzfile("../../data/agribusiness/clean/soybean_brazil.csv.gz","rt"), header=T))}
  if(n==3){df = setDT(read.csv(gzfile("../../data/agribusiness/clean/maize_brazil.csv.gz","rt"), header=T))}
  if(n==4){df = setDT(read.csv(gzfile("../../data/agribusiness/clean/beef_brazil.csv.gz","rt"), header=T))}
 
  df = df[id_type=='county_known' & destination!='BRAZIL',]
  if(n==1){df$origin_id = df$county_id}else{df$origin_id = df$amc_id}
  df$destination_id = df$destination
  df$product = df$commodity
  df_p = df[, c('year','origin_id','destination_id','destination_bloc','product','fob_usd','equivalent_tonnes')][, lapply(.SD, sum, na.rm=TRUE), by=list(year,origin_id,destination_id,destination_bloc,product)]
  
  df_ijt = df_p[,c('year','origin_id','destination_id','destination_bloc','product','fob_usd','equivalent_tonnes')][, lapply(.SD, sum, na.rm=TRUE), by=list(year,origin_id,destination_id,destination_bloc,product)]
  setnames(df_ijt, old = c('fob_usd','equivalent_tonnes'),  new = c('X_ijt','Q_ijt'))
  df_njt = df_p[,c('year','destination_id','destination_bloc','product','fob_usd','equivalent_tonnes')][, lapply(.SD, sum, na.rm=TRUE), by=list(year, destination_id, destination_bloc, product)]
  setnames(df_njt, old = c('fob_usd','equivalent_tonnes'),  new = c('X_njt','Q_njt'))
  df_xqt = merge(df_ijt, df_njt, by=c('year','destination_id','destination_bloc','product'))
  
  min_year = c(min(df_p$year))
  df_ij = df_p[year %in% min_year, c('origin_id','destination_id','destination_bloc','product','fob_usd','equivalent_tonnes')][, lapply(.SD, sum, na.rm=TRUE), by=list(origin_id,destination_id,destination_bloc,product)]
  setnames(df_ij, old = c('fob_usd','equivalent_tonnes'),  new = c('X_ij','Q_ij'))
  df_j = df_p[year %in% min_year, c('destination_id','destination_bloc','product','fob_usd','equivalent_tonnes')][, lapply(.SD, sum, na.rm=TRUE), by=list(destination_id,destination_bloc,product)]
  setnames(df_j, old = c('fob_usd','equivalent_tonnes'),  new = c('X_j','Q_j'))
  df_i = df_p[year %in% min_year, c('origin_id','product','fob_usd','equivalent_tonnes')][, lapply(.SD, sum, na.rm=TRUE), by=list(origin_id,product)]
  setnames(df_i, old = c('fob_usd','equivalent_tonnes'),  new = c('X_i','Q_i'))
  df_xq = merge(df_ij, df_j, by=c('destination_id','destination_bloc','product'))
  df_xq = merge(df_xq, df_i, by=c('origin_id','product'))
  
  df = merge(df_xqt, df_xq, by=c('origin_id','destination_id','destination_bloc','product'))
  if(n==1){df$origin_nation='ARGENTINA'}else{df$origin_nation='BRAZIL'}
  df = df[,c('year','origin_nation','origin_id','destination_id','destination_bloc','product','X_ijt','Q_ijt','X_njt','Q_njt','X_ij','Q_ij','X_i','Q_i','X_j','Q_j')]
  
  if(n==1){df_arsoy=df}
  if(n==2){df_brsoy=df}
  if(n==3){df_brmaize=df}
  if(n==4){df_brbeef=df}
  
}

df_trade = rbind(df_arsoy,df_brsoy,df_brmaize,df_brbeef)

df = setDT(read.csv(gzfile("../../data/trade/clean/world_temperature_change.csv.gz","rt"), header=T))
df[, origin_nation:=toupper(country)]
df = df[months=='Sep–Oct–Nov' & element=='Temperature change',]
df$value = abs(df$value)
df = df[, c('year','value','origin_nation')]
setnames(df, old = c('value'),  new = c('W_nt'))
df_weather = df
df = merge(df_trade, df_weather, by=c('year','origin_nation'))

df_e = unique(df[,c('origin_id', 'destination_id')])
df_e$ij = paste0(df_e$origin_id,'_',df_e$destination_id)
df = merge(df, df_e, by=c('origin_id','destination_id'))

df_e = unique(df[,c('origin_nation', 'destination_id')])
df_e$nj = paste0(df_e$origin_nation,'_',df_e$destination_id)
df = merge(df, df_e, by=c('origin_nation','destination_id'))

df_e = unique(df[,c('destination_id','year','product')])
df_e$jtc = paste0(df_e$destination_id,'_',df_e$year,'_',df_e$product)
df = merge(df, df_e, by=c('destination_id','year','product'))

df_e = unique(df[,c('origin_nation','destination_id','year','product')])
df_e$njtc = paste0(df_e$origin_nation,'_',df_e$destination_id,'_',df_e$year,'_',df_e$product)
df = merge(df, df_e, by=c('origin_nation','destination_id','year','product'))

write.csv(df, gzfile("inputs/data_demand_county_commodity.csv.gz"), row.names = FALSE)



################################### Nation-level

df = fread("../../data/trade/clean/tradeflows_faostat.csv.gz")
df_njt = df[X_njt>0 & Q_njt>0,]
min_year = min(df_njt$year)
df_nj = df_njt[year==min_year, list(origin_id, destination_id,product,X_njt,Q_njt)]

setnames(df_nj, old = c('X_njt','Q_njt'),  new = c('X_nj','Q_nj'))
df_j = df_nj[, c('destination_id','product','X_nj','Q_nj')][, lapply(.SD, sum, na.rm=TRUE), by=list(destination_id,product)]
setnames(df_j, old = c('X_nj','Q_nj'),  new = c('X_j','Q_j'))
df_n = df_nj[, c('origin_id','product','X_nj','Q_nj')][, lapply(.SD, sum, na.rm=TRUE), by=list(origin_id,product)]
setnames(df_n, old = c('X_nj','Q_nj'),  new = c('X_n','Q_n'))
df_xq = merge(df_nj, df_j, by=c('destination_id','product'))
df_xq = merge(df_xq, df_n, by=c('origin_id','product'))
df = merge(df_njt, df_xq, by=c('origin_id','destination_id','product'))
df_trade = df[,c('year','origin_id','destination_id','product','X_njt','Q_njt','X_jt','Q_jt','X_nj','Q_nj','X_n','Q_n','X_j','Q_j')]

df = read.csv(gzfile("../../data/trade/clean/world_temperature_change.csv.gz","rt"), header=T)
setDT(df)
df[, origin_id:=country]
df = df[months=='Sep–Oct–Nov' & element=='Temperature change',] 
df$value = abs(df$value)
df = df[, c('year','value','origin_id')]
setnames(df, old = c('value'),  new = c('W_nt'))
df_weather = df
df = merge(df_trade, df_weather, by=c('year','origin_id'))

df_e = unique(df[,c('origin_id', 'destination_id')])
df_e$nj = paste0(df_e$origin_id,'_',df_e$destination_id)
df = merge(df, df_e, by=c('origin_id','destination_id'))

df_e = unique(df[,c('destination_id','year','product')])
df_e$jtc = paste0(df_e$destination_id,'_',df_e$year,'_',df_e$product)
df = merge(df, df_e, by=c('destination_id','year','product'))

df = df[,c('year','origin_id','destination_id','product','X_njt','Q_njt','X_jt','Q_jt','X_nj','Q_nj','X_n','Q_n','X_j','Q_j','W_nt','nj','jtc')]
df[, origin_id:=toupper(origin_id)][, destination_id:=toupper(destination_id)]
write.csv(df, gzfile("inputs/data_demand_nation.csv.gz"), row.names = FALSE)



################################### Commodity-level

df = fread("inputs/data_demand_nation.csv.gz")
df[, c('s_nj') := list(X_nj/X_j)][, c('z_jtc') := list(W_nt*s_nj)]
df_trade = df

df_jtc = df_trade[,c('year','destination_id','product','X_njt','Q_njt','z_jtc')][, lapply(.SD, sum, na.rm=TRUE), by=list(year,destination_id,product)]
setnames(df_jtc, old = c('X_njt','Q_njt'),  new = c('X_jtc','Q_jtc'))
df_jt = df_trade[,c('year','destination_id','X_njt')][, lapply(.SD, sum, na.rm=TRUE), by=list(year,destination_id)]
setnames(df_jt, old = c('X_njt'),  new = c('X_jt'))

df = setDT(read.csv(gzfile("../../data/trade/clean/world_producer_prices.csv.gz","rt"), header=T))
df[, origin_id:=toupper(origin_id)]
df = merge(df_trade, df, by=c('year','origin_id','product'))
df_pp = df[,list(year,destination_id, product, p_ntc)][, lapply(.SD, mean, na.rm=TRUE), by=list(year,destination_id,product)]
setnames(df_pp, old = c('p_ntc'),  new = c('p_jtc_producer'))

df_e = unique(df[,c('year','destination_id')])
df_e$jt = paste0(df_e$year,'_',df_e$destination_id)

df = merge(df_jtc, df_pp, by=c('year','destination_id','product'))
df = merge(df, df_jt, by=c('year','destination_id'))
df = merge(df, df_e, by=c('year','destination_id'))

df[, c('X_jtc_X_jt','p_jtc') := list( X_jtc/X_jt, X_jtc/Q_jtc )]
df = df[,c('year','destination_id','product','X_jtc_X_jt','X_jtc','X_jt','Q_jtc','p_jtc','z_jtc','jt')]
write.csv(df, gzfile("inputs/data_demand_commodity.csv.gz"), row.names = FALSE)

